Rules and recommendations for developing using the SQL language.

SQL

Structured Query Language

MDF

Main Database File. File contains all actual data. For example: DatabaseName.mdf

LDF

Log Data File. File contains all actions which has been done with data and still in progress (If FULL of BULK logging mode is enabled) (For example: DatabaseName.LDF). If you have not truncated your log file from the beginning of database creation then you can recreate *.MDF file at any point in the time.

Page

A unit of storage. Specifically speaking with regard to to SQL Server, a page is 8K in size and is the smallest unit of I/O in terms of data. Server moves data in chunks called 'Pages' because of caching.

When a table or log is stored on disk, it is stored in 8K chunks (and most of the time SQL Server allocates 8 – 8K chunks to objects). A 64KB block of the database is called an extent. SQL Server allocates extents once an object reaches a minimum size (which is also 64KB) to try to keep an object more contiguous.

Dirty Page

Data or log page modified after entered into the buffer cache, but the modifications have not yet been written to disk.

CHECKPOINT

Command which forces all dirty pages for the current database to be written to disk.

SQL Server 2000 always generates automatic checkpoints. The interval between automatic checkpoints is based on the number of records in the log, not time. The time interval between automatic checkpoints can be highly variable. Checkpoints truncate the unused/inactive portion of the transaction log if the database is using the SIMPLE recovery model. The log is not truncated by checkpoints if the database is using the FULL or BULK recovery models.

Heap

Table without a clustered index.

Redundant Index

Clustered index on columns that is already "covered".

DML

Data Manipulation Language. Commands used to update, insert, and delete records.

The most important DML statements in SQL are:

SELECT

Extracts data from a database table.

UPDATE

Updates data in a database table.

DELETE

Deletes data from a database table.

INSERT INTO

Inserts new data into a database table.

DDL

Data Definition Language. Commands used to create, alter or delete database tables.

You can also define indexes (keys), specify links between tables, and impose constraints between database tables. The most important DDL statements in SQL are:

CREATE TABLE

Creates a new database table.

ALTER TABLE

Alters (changes) a database table.

DROP TABLE

Drops (deletes) a database table.

CREATE INDEX

Creates an index (search key).

DROP INDEX

Drops (deletes) an index.

TRANSACTION

One action or set of actions on SQL Database.

Actions can be done with commands of DML or DDL Programming Languages.

Listed DML and DDL statements above, if single and NOT enclosed in BEGIN TRAN…COMMIT TRAN block are treated by SQL server as an AUTO COMMIT transactions. You can wrap commands into one set:

-- Set lock type for current session which will be used inside transaction (described below).
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRANSACTION
    SELECT * FROM table1
    SELECT * FROM table2
    ...
COMMIT TRANSACTION
-- Set lock type for current session to default value (described below).
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Phantom data

New data inserted inside transaction.

Dirty data

Data updated inside transaction.

Dirty read

Action then transaction tries to read dirty data created by other transaction.

Isolation

Restricting access to database items by locking them (tables, rows...).

When SQL Server executes TRANSACTION it isolates parts of data inside database so for example two users can't UPDATE one data row at the same time. Isolation is done by locking database items. These are isolation types:

READ COMMITTED

Default lock type. Specifies that shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in non repeatable reads or phantom data. This option is the SQL Server default.

READ UNCOMMITTED

Implements dirty read, or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honoured. When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the four isolation levels.

REPEATABLE READ

Locks are placed on all data that is used in a query, preventing other users from updating the data, but new phantom rows can be inserted into the data set by another user and are included in later reads in the current transaction. Because concurrency is lower than the default isolation level, use this option only when necessary.

SERIALIZABLE

Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

Exclusive lock

This lock mode is very straightforward, a group of records are taken, (row, page, extent, table or database), and are held exclusively by one transaction. By default when an INSERT, UPDATE, or DELETE statement runs, an Exclusive Lock will be issued. No other operation of any kind, (read or DML), can use the records held by an this lock.

Shared lock

Applied on records read by a SELECT statement. It is designed to allow concurrent read access from other transactions, but none can modify the held records. This lock enables reads to comply with ACID by disallowing records to be changed at the same instant a read occurs. Only committed data can be read. Shared lock manipulation is one of the areas we will gain performance by controlling.

Update Lock

An Update Lock stops this type of deadlock from occurring. When a transaction signals intent to convert, an Update lock is requested. Only one transaction can obtain an Update lock on the selected resource at a time. When the records are actually modified, the Update lock will be converted to an Exclusive lock. By only allowing one transaction at a time to obtain an Update lock, the deadlocking on conversion requests is eliminated. This type of lock can be understood by looking at an update statement with a WHERE clause. Before the update can complete, the records meeting the WEHRE clause must be selected. Rather than use a shared lock for this initial select, an Update lock will be requested.

Deadlock

Condition in which one resource is waiting on the action of a second, while that second action is waiting on the first.

A deadlock on the other hand, means there is no way to finish. Your transaction is stuck in a loop with some other transaction. At this point, the database system will usually pick one transaction to be killed so the other can complete.

Naming Rules

• Try to use C# naming standards. There is no need to reinvent the wheel and have multiple standards. Keep it simple.
• Use Upper case keywords (SELECT not select)
• Procedures are Pascal cased (UpdateSchemeName)
• Parameters are Pascal cased (@NewSchemeName)
• Variables are camel cased (@schemeName)

• Use singular form for table names.

  Example: [Message], [Account]. Using the plural form complicates automatic code generation scripts.

• Do not use the table name as a prefix to name columns from the same table.

  Example: Use [User].[Id] instead of [User].[UserId]. Example: Use [User].[Name] instead of [User].[UserName] Use the table name as a prefix only to indicate data from foreign table: [Role].[UserId] - refers to [User].[Id].

Data Type

• Design for international use. Use Unicode column types 'nvarchar' or 'ntext' instead plain 'varchar' or 'text'.

• Avoid null type columns. Define default values if possible. It will make code simpler and more reliable.

  Code should reflect business logic and data as close as possible. This way, developers and businesses are more on the same page. If the business data has two states: empty or value, then column must be non-NULL and store: '' or 'SomeValue' for string, 0 and SomeNumber for number. Also, everything is simpler when the NULL concept is not used, just like the 2-state CheckBox (True/False) is simpler than the 3-state CheckBox (True/False/Null). Non-nullable columns with the default value are backward compatible and won't break any code. They can be created on very large live tables in 4 steps:
  1. Create a nullable column (quick process).
  2. Specify the default value (quick process).
  3. Update all default null values with batch processing (slow process without locking).
  4. Change column type to non-nullable (quick process).
  

• Design for long term international use. Use 'bigint' to store international customers and log records. Maximum value of 'int' type' is 2,147,483,647, which is not enough for available customer in the world (8 billion).
• Use "uniqueidentifier" as the primary key type for similar data in different tables (for example, customer accounts spread across separate databases). It allows to merge data and keep the same primary keys.

Enum Tables

C# enumerations can be stored on Database under [Types] schema by using this pattern:

public enum [TableName]
{
	///<summary>[Summary]</summary>
	[Description("[Description]")]
	[Value] = [Id],
}

CREATE TABLE [Types].[TableName] (
	[Id]          INT           NOT NULL,
	[Value]       VARCHAR (20)  NOT NULL,
	[Description] VARCHAR (50)  NOT NULL,
	[Summary]     VARCHAR (100) NOT NULL,
	CONSTRAINT [PK_Types.TableName] PRIMARY KEY CLUSTERED ([Id] ASC)
);

Common Column Names

Name	Definition
Id	[uniqueidentifier|bigint|int] NOT NULL DEFAULT (newid())	Record primary key
OtherTableId	[uniqueidentifier|bigint|int] NOT NULL	Foreign key - primary key in [OtherTable]
Created	[datetime] NOT NULL DEFAULT (getdate())	Record create time
Updated	[datetime] NOT NULL DEFAULT (getdate())	Record update time
IsEnabled	[bit] NOT NULL DEFAULT ((1))	Record is enabled
Checksum	[timestamp] NOT NULL	Checksum (or time stamp) to check if row changed before new update

Examples

Table.Column	[Customer].[Id]
C# DataContext property	public virtual DataSet<Customer> Customer { get; set; }
C# Code	var customer    = db.Customer.First(); var customers   = db.Customer.All(); var customerId  = customer.Id; var customerIds = customers.Select(x => x.Id).ToArray();

1. SQL 2000 Server must generate dates with GETUTCDATE() instead of GETDATE() – in this way DB server will be completely independent from Time Zone settings of PC itself. SQL 2005 have TimeZone support already.
2. Date times must be passed between servers with time zone offsets. In this case both sides can use this extra information for proper Date Time conversions (it means that date can be submitted by any server from any Time Zone and it will be converted correctly on other side always.
3. If date times are submitted as strings (only thru GET and POST requests!) then date times must be send in proper format as described by ISO 8601 International Standard: yyyy-MM-ddThh:mm:ss.fff ±hh:ss
4. On ASP JavaScript DateTime values from different time zones can be compared as integers:
   // These dates are same: 2000-01-01 00:00:00-0800 = 2000-01-01 08:00:00Z  = 949392000000
   var IsSameDateTime = (date1. getTime() = = date2 .getTime() );
5. To format date as yyyy-MM-dd you can use:
   REPLACE ( STR ( DATEPART ( yyyy , @datetime ) , 4 ) + '-' + STR ( DATEPART ( mm , @datetime ) , 2 ) + '-' + STR ( DATEPART ( dd , @datetime ) , 2 ) , ' ' , '0' )

By following these advices code can be made time zone independent and there will be no need to play with time zones.

• Create only 1 Log file. There is no performance benefit to have multiple log files.
• Each table needs Unique Primary Key.
• Table should contain only one Primary Key. Multiple primary keys is a sign of bad data design.
• Try to avoid table aliases in WHERE or in ORDER BY.
• There is no need to have secondary data file if databases is less than 10GB in size and data usage is low.
• Always specify constraint names manually (Don't allow SQL Server to generate random names).
• Create secondary data file only if you have very large database or want to put large amounts of outdated historic data into it.
• Well designed database contains 1/2 portion of data and 2/3 of indexes for that data.
• With reasonable backups active log takes 10-20% of database size and is no larger than 1GB.
• LOG file should have less than 50 VLF parts inside.
• Use "Fast File Initialisation" for data files only as restore feature (SQL 2005) and it can reduce recovery time 2-3 times. You need to give permission for SQL Server to do this. Requires WinXP/03 and "Perform Volume Maintenance Tasks" Security permission for "MSSQLServer Service": Administrative Tools -> Local Security Policy -> Local Policies -> User Rights Management. You need to grant it to lower privilege account (SQL service account) (defaults to: Local Administrators Group).
• If you break continuity of the log chain then you will be longer able to restore database to the state before your last full backup. Switching between SIMPLE and [FULL or BULK] log mode with database update actions between modes always brakes continuation. Switching between FULL and BULK modes is OK.
• Creation scripts must contain the DROP PROCEDURE as well as any GRANT statements necessary. In SQL 2000 The ALTER statement is not acceptable because it does not update the timestamp of the stored procedure.
• Generate scripts (with SQL Enterprise manager) without descriptive headers because it makes comparison of code with checksums (MD5) impossible.
• The best place to put a large header comment block is just before the "CREATE PROCEDURE" statement or before the "AS". Comment blocks can be placed almost anywhere, but depending on where they are located in a script, they may not be saved by the compiler and will be lost.
• Best backup strategy is Full Database Backups, Differential Database Backups AND Transaction Log Backups.

• Create separate non-clustered index for GUID columns. Do not include other columns in that index. Avoid adding a clustered index to a GUID column (uniqueIdentifier data type) (unless you have a rare case then GUID is generated incrementally). GUID takes up 16-bytes of storage, more than an identify column, which in turn make the index larger, which increases I/O reads, which can hurt performance. While the performance hit will be minimal if you do decide to add a clustered index to a GUID column.
• Always access your tables on the same order. When working with stored procedures and transactions, you may find this soon. If you lock the table A then table B, always lock them in this very same order in all stored procedures. If you, by accident, lock the table B and then table A in another procedure some day you'll have a deadlock . Deadlocks can be tricky to find if the lock sequence is not carefully designed.
• Avoid clustered indexes on columns that are already "covered" by non-clustered indexes. Use the clustered index for columns that can better make use of it.
• When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead.
• When selecting a column or columns to include in your clustered index, try to select the column:
      a) Which contains the data that will most often be searched in your queries.
      b) Which contains value that  monotonically increases.
      b) Large ranges of data will be selected from this column.
• If a table has both a clustered index and non-clustered indexes, then performance will be best optimized if the clustered index is based on a single column that is as narrow as possible. This is because non-clustered indexes use the clustered index to locate data rows and because non-clustered indexes must hold the clustered keys within their B-tree structures. This not only reduces the clustered index's physical size, it also reduces the physical size of non-clustered indexes and boosts SQL Servers overall performance.
• The primary key you select for your table should not always be a clustered index. If you create the primary key and don't specify otherwise, this is the default. Only make the primary key a clustered index if you will be regularly performing range queries on the primary key or need your results sorted by the primary key.
• If you drop or change a clustered index, keep in mind that all of the non-clustered indexes also have to be rebuilt. Also keep in mind that to recreate a new clustered index, you will need free disk space equivalent to 1.2 times the size of the table you are working with. This space is necessary to recreate the entire table while maintaining the old table until the new table is recreated. Then the old table is deleted.
• When you create a clustered index, try to create it as a UNIQUE clustered index, not a non-unique clustered index. The reason for this is that while SQL Server will allow you to create a non-unique clustered index, under the surface, SQL Server will make it unique for you by adding a 4-byte "UniqueIdentifier" to the index key to guarantee uniqueness. This only serves to increase the size of the key, which increases disk I/O, which reduces performance. If you specify that your clustered index is UNIQUE when it is created, you will prevent this unnecessary overhead.
• You can defragment disk with database/log file at the OS layer with disk defrag tools. It will speedup reading and writing to it. The good news, once defragged you don’t really need to do it again (unless you have a lot of auto growth and other files on the disks).
• Do not use text fields as primary keys.
• Use WITH (NOLOCK) in your SELECT queries. It will prevent SQL Server from locking the table.
• Arrange WHERE conditions in same order as columns inside index. If you have index [ order INDEX: column1, column2] then "SELECT * FROM tableName1 WHERE column1 > X AND column2 > Y" will be faster than "SELECT * FROM tableName1 WHERE column2 > X AND column1 > Y".

If you want proper managed access to SQL servers from .NET code then use these libraries with Microsoft Visual Studio:

Microsoft SQL Server Native Client
http://msdn.microsoft.com/data/ref/sqlnative/default.aspx

Microsoft SQL Server Native Client (SQL Native Client) is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver. It contains run-time support for applications using native-code APIs (ODBC, OLE DB and ADO) to connect to Microsoft SQL Server 7.0, 2000 or 2005. SQL Native Client should be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2005 features. This redistributable installer for SQL Native Client installs the client components needed during run time to take advantage of new SQL Server 2005 features, and optionally installs the header files needed to develop an application that uses the SQL Native Client API.

Microsoft SQL Server 2005 Management Objects Collection
http://go.microsoft.com/fwlink/?linkid=54583#snac

The Management Objects Collection package includes several key elements of the SQL Server 2005 management API, including Analysis Management Objects (AMO), Replication Management Objects (RMO), and SQL Server Management Objects (SMO). Developers and DBAs can use these components to programmatically manage SQL Server 2005. Microsoft SQL Server 2005 Management Objects Collection requires Microsoft Core XML Services (MSXML) 6.0 and Microsoft SQL Server Native Client.

 Code example on how to get text of stored procedure:

string connectionString = "Data Source=localhost;Integrated Security=True";
System.Data.SqlClient.SqlConnectionStringBuilder stringBuilder;
stringBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder(connectionString);
Microsoft.SqlServer.Management.Smo.Server server;
server = new Microsoft.SqlServer.Management.Smo.Server(stringBuilder.DataSource);
Microsoft.SqlServer.Management.Smo.StoredProcedure procedure;
procedure = server.Databases["DataseName"].StoredProcedures["ProcedureName"];
// Get all text of of procedure.
string text = procedure.Parameters["Text"].ToString();


Not all propeerties are available for SQL 2005 Server. You can check server version with:

-- If this is SQL 2005 or later then...
if (server.Information.Version.Major > 8) {
    -- Do something here...
}

You can reuse SQL Connnection Properties interface:

 

You will need reference to two DLLs:

• C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\Microsoft.Data.ConnectionUI.dll
• C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\Microsoft.Data.ConnectionUI.Dialog.dll

 Two controls on your form:

• "DbaConnectionButton" Button and
• "DbaConnectionStringTextBox" TextBox.

 

and some code:

#region Database Connection Strings

private string filterConnectionString(string text)
{
	System.Text.RegularExpressions.Regex regex;
	regex = new System.Text.RegularExpressions.Regex("(Password|PWD)\\s*=([^;]*)([;]*)",
	    System.Text.RegularExpressions.RegexOptions.IgnoreCase);
	return regex.Replace(text, "$1=<hidden>$3");
}

/// <summary>
/// Database Administrative Connection String.
/// </summary>
private string dbaConnectionString
{
	get { return m_dbaConnectionString; }
	set
	{
		this.m_dbaConnectionString = value;
		DbaConnectionStringTextBox.Text = filterConnectionString(value);
	}
}
private string m_dbaConnectionString;

private void DbaConnectionButton_Click(object sender, EventArgs e)
{
	Microsoft.Data.ConnectionUI.DataConnectionDialog dcd;
	dcd = new Microsoft.Data.ConnectionUI.DataConnectionDialog();
	//Adds all the standard supported databases
	//DataSource.AddStandardDataSources(dcd);
	//allows you to add datasources, if you want to specify which will be supported 
	dcd.DataSources.Add(Microsoft.Data.ConnectionUI.DataSource.SqlDataSource);
	dcd.SetSelectedDataProvider(Microsoft.Data.ConnectionUI.DataSource.SqlDataSource,
	    Microsoft.Data.ConnectionUI.DataProvider.SqlDataProvider);
	dcd.ConnectionString = this.dbaConnectionString;
	Microsoft.Data.ConnectionUI.DataConnectionDialog.Show(dcd);
	if (dcd.DialogResult == DialogResult.OK)
	{
		this.dbaConnectionString = dcd.ConnectionString;
	}
}

#endregion

Use XML Comments for SQL server similar to object oriented languages. In this case comments can be parsed into CHM or XHTML help files easier. In SQL 2005 parameter descriptions can be pulled out from extended properties of cells. 

--- <summary>
--- Insert new record into table.
--- </summary>
--- <param name="RecordId">Unique record Id. Use '-1' for auto value.</param>
--- <param name="RecordGuid">Set Global Unique Identifier.</param>
--- <param name="SomeValue">Set record value.</param>
--- <param name="RecordEnable">Enable or disable record.</param>
--- <remarks>
--- History:
---     2007-11-02 - Created by John Smith
--- </remarks>
CREATE PROCEDURE [dbo].[solution_Category_InsertRecord] (
     @RecordId Int,
     @RecordGuid UniqueIdentifier,
     @SomeValue NVarChar(200),
     @RecordEnabled Bit )
AS
-- Stored procedure starts here...

Uninstallation  / Update / Installation must be done in this order to avoid relation conflicts

1. Drop Foreign Key Constraints.
2. Drop Default Value Constraints.
3. Drop Check Constraints.
4. Drop Primary Key Constraints.
5. Drop Indexes.
6. Alter Tables.
7. Create Indexes.
8. Create Primary Key Constraints.
9. Create Check Constraints.
10. Create Default Value Constraints.
11. Create Foreign Key Constraints.

Fix link between SQL login and database user after attaching database.

USE [DataBaseName]
-- Create SQL Server Login
CREATE LOGIN [DatabaseAdminUserName] WITH
PASSWORD=N'password',
DEFAULT_DATABASE=[DataBaseName],
DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF,
CHECK_POLICY=OFF
GO
ALTER LOGIN [DatabaseAdminUserName] DISABLE
-- Map SQL Server Login to Database User.
exec sp_change_users_login 'AUTO_FIX', 'DatabaseAdminUserName'
ALTER LOGIN [DatabaseAdminUserName] ENABLE

Execute all .sql files in a folder without executing them individually from command line

for %z in (*.sql) do osql -S <server> -U <username> -P <password> -d <datbasename> -i %z

  With SELECT we can use Lock Hint WITH (READUNCOMMITTED) or WITH (NOLOCK). NOLOCK with SELECT is better because word READUNCOMMITTED is reserved for other purpose. So:

    a) Use READ [UN]COMMITTED to SET Transaction Isolation Level. 
    b) Use NOLOCK with SELECT for single "auto commit" transaction.

In this way you are logically separating how lock is used (on single query or on Transaction/Procedure). This helps when you will try to find stored procedures by different lock area or use these words in conversation with other developers.

Object Oriented Style

Stored Procedures:
Naming: <SolutionName>_<CategoryName>_<ActionName><ObjectName>[By<Condition>]
Example: crm_Company_GetEmployeesByDepartment

Type prefix were replaced by solution name because prefix lost is usefulness with new version of SQL server and object oriented languages which separates objects by type perfectly. Also solution name makes much more sense when couple of solutions are integrated into one database.

There are two naming styles for database objects:

Hungarian Style (Outdated):

Stored Procedures:
Naming: <TypePrefix>_<CategoryName>_<ActionName><ObjectName>[By<Condition>]
Example: up_Company_GetEmployeesByDepartment

Ext	Pfx	Name	Parent	Naming
TAB	TB	Table	Database	TB_ + Table Name
PRC	UP	User Procedure	Database	UP_ + Action + Object [+ Condition]
PRC	SP	Stored Procedure	Database	SP_ + Action + Object [+ Condition]
	XP	eXtended Procedure	Database	XP_ + Action + Object [+ Condition]
VIW	VI	View	Database	VI_ + Table Name
	FK	Foreign Key Constraint	Table	FK_ + Foreign Table Name + _ + Primary Key Table Name
	DF	Default Constraint	Column	DF_+ Table Name + _ + Column Name
	PK	Primary Key Constraint	Index	PK_ + Table Name [+ _ + Column Name]
	CK	Check Constraint	Index	CK_ + Table Name [+ _ + Column Name]
	UQ	Unique Constraint	Index	UQ_ + Table Name [+ _ + Column Name]
IDX	IX	Index	Table	IX_ + Table Name [+ _ + Column Name]
TRG	TR	Trigger	Database, Table
LGN		Login
USR		User
DBS		Database

Use 'UP_' as prefix for user stored procedures because Microsoft use 'sp_' prefix for shared stored procedures.
Use '#' prefix for temporary table names inside scripts.

File Names

Type	Extension
Function	ObjectName.function.sql
Index	ObjectName.index.sql
Stored Procedure	ObjectName.proc.sql
Table	ObjectName.table.sql
Trigger	ObjectName.trigger.sql
View	ObjectName.view.sql
Primary Key Constraint	ObjectName.pkey.sql
Foreign Key Constraint	ObjectName.fkey.sql
Unique Key Constraint	ObjectName.ukey.sql
Check Constraint	ObjectName.chkconst.sql
Default Constraint	ObjectName.defconst.sql
Statistic	ObjectName.statistic.sql

File	Location
Main Database File	D:\SQLDATA\DatabaseName.MDF
Non-Primary Database File	D:\SQLDATA\DatabaseName.NDF
Log Data File	D:\SQLLOGS\DatabaseName.LDF
Virtual Log File	VLF files exists inside *.LDF file
Full Database Backup File	D:\SQLBACK\DatabaseName_yyyMMdd_HHmmss_full.BAK
Differential Database Backup File	D:\SQLBACK\DatabaseName_yyyMMdd_HHmmss_diff.DIF
Transaction Log Backup File	D:\SQLBACK\DatabaseName_yyyMMdd_HHmmss_logs.TRN

Other Rules

• Procedure with аction name Insert, Select, Delete and Update must affect all columns of single record and use primary key as condition:

Prefix	Name	Examples
Insert	INSERT one record into table	globalization_Languages_InsertRecord *
Select	SELECT one record from table	globalization_Languages_SelectRecord *
Select	SELECT all records from table	globalization_Languages_SelectRecords
Update	UPDATE one record inside table	globalization_Languages_UpdateRecord *
Delete	DELETE one record from table	globalization_Languages_DeleteRecord *

* - Procedures used to create .NET DataSet TableAdapter object.

Otherwise use these prefixes:

Prefix	Name	Examples
Add	Add record(s) to one or multiple tables.	aspnet_UsersInRoles_AddUsersToRoles
Get	Get record(s) from one or multiple tables.	aspnet_Membership_GetUserByName
Set	Set record(s) of one or multiple tables.	aspnet_Membership_SetPassword
Upsert	Insert or update record by unique condition/index.	crm_UserTicks_UpsertUserTick
Create	Insert record and adjust records in other tables.	aspnet_Membership_CreateUser
Modify	Update record and adjust records in other tables.	crm_Company_ModifyUserInfo
Remove	Delete record and adjust records in other tables.	aspnet_Setup_RemoveAllRoleMembers
Change	Replace cell value in table.	crm_Tickets_ChangeTicketStatus

CREATE PROCEDURE [dbo].[solution_Category_InsertRecord] (
    @RecordId int,
    @RecordGuid uniqueIdentifier,
    @SomeValue int,
    @RecordEnabled bit
) AS

-- You can call this procedure with this command.
-- EXEC solution_Category_InsertRecord null, null, 135, 1

-- If @RecordGuid was not specified then generate new one.
IF @RecordGuid = '00000000-0000-0000-0000-000000000000' 
SET @RecordGuid = newId()

INSERT INTO [dbo].[SomeTable] (
    [RecordId],
    [RecordGuid],
    [SomeValue],
    [RecordEnabled]
) VALUES (
    @RecordId,
    @RecordGuid,
    @SomeValue,
    @RecordEnabled
)

-- Use SCOPE_IDENTITY() to return [RecordId] value if [RecorId] if auto-seeded.

-- Return new values so user don't need to query database again to get them.
SELECT @RecordId as 'RecordId'
-- Or you can select all cells
SELECT * FROM dbo.SomeTable WHERE [RecordId] = @RecordId

• Think object oriented. Use GUID object to identify objects stored inside record (for example "UserGuid"). In this case you can use primary id column only for record numbering and you can drop and create it to renumber records (if some of them were deleted). With GUIDs will be no problem to merge two tables (for example to merge two account tables of different companies) And objects will be more flexible to interact with each other. You can have sol_Relations [RelationGuid, PrimaryGuid, ForeignGuid, RecordEnabled] table to store all relations between objects of different type.